﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Collections;

namespace LdExpress
{
    public class PaymentRequest
    {
        public long ID = -1;
        public DateTime DateCreate = DateTime.Now;
        public string AllFileNumber = "";
        public long IdProvince = 0;
        public long PaymentSubjectId = 0;
        public long SubjectType = 0; //Loai hinh thanh toan : 1-THU KH,2-THU KHÁC,3-TTNCC,4-TTCOM NCC,5-TTCOM KH,6-TT KHÁC
        public string PaymentSubject = "";
        public string Description = "";
        public string BankName = "";
        public int ReciprocalAccount_Had = 0;
        public int ReciprocalAccount_Debt = 0;
        public double SumTotal = 0;
        public double SumRequest = 0;
        public string PaymentRequestNumber = "";
        public string Status = "";
        public string Note = "";
        public string CreateByName = "";
        public string AccountingName = "";
        public string VerifyName = "";
        public string DirectorName = "";
        public int AccountingVerify = 0;
        public int DirectorVerify = 0;
        public int Verify = 0;
        public int SumAmount = 0;
        public DateTime LastUpdate = DateTime.Now;


        public PaymentRequest()
        {
        }

        public PaymentRequest(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_PaymentRequest";

            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = long.Parse(DBModule.GetNewID(typeof(Bill), "tbl_bill" , "ID", "ID", cn, trans));
                strSQL = "Insert into " + strTableName + " (" +
                                "DateCreate," +
                                "AllFileNumber," +
                                "PaymentSubject," +
                                "PaymentSubjectId," +
                                "SubjectType," +
                                "Description," +
                                "BankName," +
                                "ReciprocalAccount_Had," +
                                "ReciprocalAccount_Debt," +
                                "SumTotal," +
                                "SumRequest," +
                                "PaymentRequestNumber," +
                                "Status," +
                                "Note," +
                                "CreateByName," +
                                "AccountingName," +
                                "VerifyName," +
                                "DirectorName," +
                                "Verify," +
                                "AccountingVerify," +
                                "DirectorVerify," +
                                "IdProvince," +
                                "SumAmount," +
                                "LastUpdate" +
                             ") " +
                         " values(" +
                              DBModule.RefineDatetime(DateCreate) +
                             ",'" + DBModule.RefineString(AllFileNumber) + "'" +
                             "," + "'" + DBModule.RefineString(PaymentSubject) + "'" +
                             "," + PaymentSubjectId.ToString() +
                             "," + SubjectType.ToString() +
                             "," + "'" + DBModule.RefineString(Description) + "'" +
                             "," + "'" + DBModule.RefineString(BankName) + "'" +
                             "," + ReciprocalAccount_Had.ToString() +
                             "," + ReciprocalAccount_Debt.ToString() +
                             "," + SumTotal.ToString() +
                             "," + SumRequest.ToString() +
                             ",'" + DBModule.RefineString(PaymentRequestNumber.ToString()) + "'" +
                             ",'" + DBModule.RefineString(Status.ToString()) + "'" +
                             ",'" + DBModule.RefineString(Note.ToString()) + "'" +
                             ",'" + DBModule.RefineString(CreateByName.ToString()) + "'" +
                             ",'" + DBModule.RefineString(AccountingName.ToString()) + "'" +
                             ",'" + DBModule.RefineString(VerifyName.ToString()) + "'" +
                             ",'" + DBModule.RefineString(DirectorName.ToString()) + "'" +
                             "," + Verify.ToString() +
                             "," + AccountingVerify.ToString() +
                             "," + DirectorVerify.ToString() +
                             "," + IdProvince.ToString() +
                             "," + SumAmount.ToString() +
                             "," + DBModule.RefineDatetime(LastUpdate) +
                             ")";
                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("paymentrequest", "add", cn, trans);              
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                        "DateCreate = " + DBModule.RefineDatetime(DateCreate) + "," +
                        "AllFileNumber =  '" + DBModule.RefineString(AllFileNumber) + "'," +
                        "IdProvince =  " + IdProvince.ToString() + "," +
                        "PaymentSubjectId =  " + PaymentSubjectId.ToString() + "," +
                        "SubjectType =  " + SubjectType.ToString() + "," +
                        "PaymentSubject = '" + DBModule.RefineString(PaymentSubject) + "'," +
                        "Description = '" + DBModule.RefineString(Description) + "'," +
                        "BankName = '" + DBModule.RefineString(BankName) + "'," +
                        "ReciprocalAccount_Had = " + ReciprocalAccount_Had + "," +
                        "ReciprocalAccount_Debt = " + ReciprocalAccount_Debt + "," +
                        "PaymentRequestNumber = '" + DBModule.RefineString(PaymentRequestNumber.ToString()) + "'," +
                        "Status = '" + DBModule.RefineString(Status.ToString()) + "'," +
                        "Note = '" + DBModule.RefineString(Note.ToString()) + "'," +
                        "CreateByName = '" + DBModule.RefineString(CreateByName.ToString()) + "'," +
                        "AccountingName = '" + DBModule.RefineString(AccountingName.ToString()) + "'," +
                        "VerifyName = '" + DBModule.RefineString(VerifyName.ToString()) + "'," +
                        "DirectorName = '" + DBModule.RefineString(DirectorName.ToString()) + "'," +
                        "DirectorVerify = " + DirectorVerify.ToString() + "," +
                        "AccountingVerify=" + AccountingVerify.ToString() + "," +
                        "Verify=" + Verify.ToString() + "," +
                        "IdProvince = " + IdProvince.ToString() + "," +
                        "SumTotal = " + SumTotal.ToString() + "," +
                        "SumRequest = " + SumRequest.ToString() + "," +
                        "SumAmount = " + SumAmount.ToString() + "," +
                        "LastUpdate = " + DBModule.RefineDatetime(LastUpdate) +
                        " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("paymentrequest", "edit", cn, trans);                
            }
            //UpdateReceiveAndPaymentReport(this, cn, trans);
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {

            string strSQL = "DELETE FROM tbl_PaymentRequest WHERE ID = " + this.ID.ToString() + ";";
            strSQL += "DELETE from tbl_paymentrequest_detail where PaymentRequestID=" + this.ID.ToString();
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("paymentrequest", "delete", cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_PaymentRequest WHERE ID = " + ID.ToString() + " limit 0,1";

            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("DateCreate")) DateCreate = DateTime.Parse(dr["DateCreate"].ToString());
                if (!dr.IsNull("PaymentRequestNumber")) PaymentRequestNumber = dr["PaymentRequestNumber"].ToString();
                if (!dr.IsNull("AllFileNumber")) AllFileNumber = dr["AllFileNumber"].ToString();
                if (!dr.IsNull("IdProvince")) IdProvince = long.Parse(dr["IdProvince"].ToString());
                if (!dr.IsNull("PaymentSubjectId")) PaymentSubjectId = long.Parse(dr["PaymentSubjectId"].ToString());
                if (!dr.IsNull("PaymentSubject")) PaymentSubject = dr["PaymentSubject"].ToString();
                if (!dr.IsNull("SubjectType")) SubjectType = long.Parse(dr["SubjectType"].ToString());
                if (!dr.IsNull("BankName")) BankName = dr["BankName"].ToString();
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("ReciprocalAccount_Had")) ReciprocalAccount_Had = int.Parse(dr["ReciprocalAccount_Had"].ToString());
                if (!dr.IsNull("ReciprocalAccount_Debt")) ReciprocalAccount_Debt = int.Parse(dr["ReciprocalAccount_Debt"].ToString());
                if (!dr.IsNull("Status")) Status = dr["Status"].ToString();
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("Note")) Note = dr["Note"].ToString();
                if (!dr.IsNull("CreateByName")) CreateByName = dr["CreateByName"].ToString();
                if (!dr.IsNull("AccountingName")) AccountingName = dr["AccountingName"].ToString();
                if (!dr.IsNull("VerifyName")) VerifyName = dr["VerifyName"].ToString();
                if (!dr.IsNull("DirectorName")) DirectorName = dr["DirectorName"].ToString();
                if (!dr.IsNull("IdProvince")) IdProvince = long.Parse(dr["IdProvince"].ToString());
                if (!dr.IsNull("Verify")) Verify = int.Parse(dr["Verify"].ToString());
                if (!dr.IsNull("DirectorVerify")) DirectorVerify = int.Parse(dr["DirectorVerify"].ToString());
                if (!dr.IsNull("AccountingVerify")) AccountingVerify = int.Parse(dr["AccountingVerify"].ToString());
                if (!dr.IsNull("SumTotal")) SumTotal = double.Parse(dr["SumTotal"].ToString());
                if (!dr.IsNull("SumRequest")) SumRequest = double.Parse(dr["SumRequest"].ToString());
                if (!dr.IsNull("SumAmount")) SumAmount = int.Parse(dr["SumAmount"].ToString());
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
            }
        }

        public static void GetList(string Condittion, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select *," +
                " CASE subjectType  WHEN 1 THEN 'THU KH' " +
                " WHEN 2 THEN 'THU KHÁC'" +
                " WHEN 3 THEN 'TTNCC'" +
                " WHEN 4 THEN 'TTCOM NCC'" +
                " WHEN 5 THEN 'TTCOM KH'" +
                " WHEN 6 THEN 'TT KHÁC'" +
                " ELSE ''" +
                " END AS subjectTypeName" +
            " from tbl_PaymentRequest where 1=1 ";
            if ((Condittion != "") && (Condittion != null))
            {
                strSQL += Condittion;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public void DoSearch(string strKeywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_PaymentRequest where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
    }
}

